package com.dao;

/*
 * Author: Deepika Gohil
 * 
 * Description: This file handles creating a new movie, edit movie, delete movie,
 * issue movie, submit movie, generate bill, get all categories, get all movies,
 * get movies depending on the category etc. All the movie relate operations are 
 * covered in this file.
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.beans.*;
import com.constants.*;
import com.exceptions.InvalidCategoryIdException;
import com.exceptions.MovieAlreadyExistsException;
import com.exceptions.MovieUnavailableException;
import com.exceptions.ServiceUnavailableException;
import com.exceptions.UserNotEligibleException;
import com.exceptions.MovieNotIssuedtoUserException;
import com.exceptions.PendingIssuedMoviesException;
import com.exceptions.MovieAlreadyIssuedException;
import com.exceptions.UserHasNotIssuedAnyMovieException;
import com.exceptions.MovieNotExistsException;
import com.exceptions.InvalidEnteriesException;
import com.utility.*;

public class moviedao {
	
	String available = "Available!";
	String notAvailable = "Not Available!";
	
	/* 
	 * get list of all the Category IDs.
	 */
	public int[] getAllCatIds() throws ServiceUnavailableException
	{
		int[] catagoryIds = null;
		int size = 0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			con = DBConnection.getConnection();
			if(con == null)
			{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
			}

			System.out.println("\n --------------------");
			System.out.println("\n Get all catagory IDs!");
			System.out.println("\n --------------------");
			
			String pStatement = SQLConstants.GETALLCATEGORYIDS;
			prestmt = con.prepareStatement(pStatement);
			
			System.out.println("\n Query to get all category IDs is:" + prestmt.toString());
			
			rs = prestmt.executeQuery();
			
			 if (rs != null) 
			 {
	         rs.last();
	         
	         size = rs.getRow();
	         catagoryIds = new int[size];
	         
	         int currRow = 0;
	         
	         rs.beforeFirst();
	         	while (rs.next())
		         	{	
	         		catagoryIds[currRow] = (int)rs.getInt(1);		
					currRow++;
		         	}
	         	System.out.println("\n RESULT: IDs of all the categories has been fetched!.");
			 }
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
			
			System.out.println("\n returning all movie ids.");		
			return catagoryIds;	
		}
		catch(SQLException e){
			e.printStackTrace();
			return catagoryIds;	
		} 	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	}
	
	/* 
	 * get the category Names depending on the category ID. 
	 */
	public String getCategoryName(int categoryId) throws ServiceUnavailableException, InvalidCategoryIdException
	{
		String CategoryName="";
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			con = DBConnection.getConnection();
			if(con == null)
			{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
			}

			System.out.println("\n -------------------------------------");
			System.out.println("\n Get category Name on the category ID!");
			System.out.println("\n -------------------------------------");
			
			String pStatement = SQLConstants.GETCATEGORYLIST;
			prestmt = con.prepareStatement(pStatement);
			
			prestmt.setInt(1, categoryId);
			
			System.out.println("\n Query to the category Name depending on category id is:" + prestmt.toString());
			
			rs = prestmt.executeQuery();
			if(rs.next())
		    {	
				CategoryName = (String)rs.getString(1);
		    }
			else
			{
				throw new InvalidCategoryIdException();
				
			}
	        System.out.println("\n RESULT: Category Name for the specific category has been fetched!.");
			
	        // close all the connections.
	        System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
			
			System.out.println("\n returning the category name.");		
			return CategoryName;	
		}
		catch(SQLException e){
			e.printStackTrace();
			return CategoryName;	
		} 	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	}	
	
	/* 
	 * get list of all the movies.
	 */
	public Movie[] getAllMovies() throws ServiceUnavailableException
	{
		Movie[] movielist = null;
		int size = 0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			con = DBConnection.getConnection();
			if(con == null)
			{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
			}

			System.out.println("\n -------------------");
			System.out.println("\n Get all the movies!");
			System.out.println("\n -------------------");
			
			String pStatement = SQLConstants.GETALLMOVIELIST;
			prestmt = con.prepareStatement(pStatement);
			
			System.out.println("\n Query to get all the movies is:" + prestmt.toString());
			
			rs = prestmt.executeQuery();
			
			 if (rs != null) 
			 {
	         rs.last();
	         
	         size = rs.getRow();
	         movielist = new Movie[size];
	         
	         int currRow = 0;
	         
	         rs.beforeFirst();
	         	while (rs.next())
		         	{	
	                Movie movie = new Movie();
					movie.setMovieId((int)rs.getInt(1));
					movie.setMovieName((String)rs.getString(2));
					movie.setMovieBanner((String)rs.getString(3));
					movie.setReleaseDate((String)rs.getString(4));
					movie.setRent((Double)rs.getDouble(5));
					movie.setCategoryid((Integer)rs.getInt(6));	
					movie.setRating((Double)rs.getDouble(7));
					movie.setNo_of_ratings((Integer)rs.getInt(8));
					movie.setAvailableCopy((Integer)rs.getInt(9));
					movie.setTotalCopy((Integer)rs.getInt(10));
					
					if(movie.getAvailableCopy()<=0)
						{
						movie.setStatus(notAvailable);
						}
					else
						{
						movie.setStatus(available);
						}		
					movielist[currRow] = movie;
					currRow++;
		         	}
	         	System.out.println("\n RESULT: List of all the movies has been fetched!.");
			 }
			 
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
			
			System.out.println("\n returning all movie ids.");		
			return movielist;	
		}
		catch(SQLException e){
			e.printStackTrace();
			return movielist;
		} 	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	}	
	
	
	/* 
	 * get list of all the movies depending on the category. 
	 */
	public Movie[] getCategoryMovies(int categoryId) throws ServiceUnavailableException
	{
		Movie[] movielist = null;
		int size = 0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			con = DBConnection.getConnection();
			if(con == null)
			{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
			}

			System.out.println("\n -------------------------------------");
			System.out.println("\n Get movies depending on the category!");
			System.out.println("\n -------------------------------------");
			
			String pStatement = SQLConstants.GETCATMOVIELIST;
			prestmt = con.prepareStatement(pStatement);
			
			prestmt.setInt(1, categoryId);
			
			System.out.println("\n Query to the movies depending on category id is:" + prestmt.toString());
			
			rs = prestmt.executeQuery();
			if (rs != null) 
			 {
	         rs.last();
	         
	         size = rs.getRow();
	         movielist = new Movie[size];
	         
	         int currRow = 0;
	         
	         rs.beforeFirst();
	         	while (rs.next())
		         	{	
	                Movie movie = new Movie();
					movie.setMovieId((int)rs.getInt(1));
					movie.setMovieName((String)rs.getString(2));
					movie.setMovieBanner((String)rs.getString(3));
					movie.setReleaseDate((String)rs.getString(4));
					movie.setRent((Double)rs.getDouble(5));				
					movie.setRating((Double)rs.getDouble(6));
					movie.setNo_of_ratings((Integer)rs.getInt(7));
					movie.setAvailableCopy((Integer)rs.getInt(8));
					movie.setTotalCopy((Integer)rs.getInt(9));
					
					movie.setCategoryid(categoryId);
					
					if(movie.getAvailableCopy()<=0)
						{
						movie.setStatus(notAvailable);
						}
					else
						{
						movie.setStatus(available);
						}		
					movielist[currRow] = movie;
					currRow++;
		         	}
	         System.out.println("\n RESULT: List of all the movies for a specific catagory has been fetched!.");
			 }
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
			
			System.out.println("\n returning all movie ids.");		
			return movielist;	
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	}	
	
	
	/*
	 * Check if the movie is already there in the database for 
	 * Create movie and update movie
	 * return 1: Exists
	 * return 0 : Doesn't Exists
	 */
	
	public int checkMovieExists(Movie m) throws ServiceUnavailableException
	{
		int exists = 0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			con = DBConnection.getConnection();
			if(con == null)
			{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
			}

			System.out.println("\n --------------------------------------------------");
			System.out.println("\n Get all the movies and compare the name && banner!");
			System.out.println("\n --------------------------------------------------");
			
			String pStatement = SQLConstants.GETALLMOVIELIST;
			prestmt = con.prepareStatement(pStatement);
			
			System.out.println("\n Query to get all the movies is:" + prestmt.toString());
			
			int MovieId = 0;
			String MovieName = "";
			String MovieBanner = "";
			String ReleaseDate = "";
			
			rs = prestmt.executeQuery();
			
			 if (rs != null) 
			 {
	         rs.last();
	         rs.beforeFirst();
	         	while (rs.next())
		         	{
	         		MovieId  = (int)rs.getInt(1);
	                MovieName = (String)rs.getString(2);
	                MovieBanner = (String)rs.getString(3);
	                ReleaseDate = (String)rs.getString(4);
					
	                //don't compare it with the same movie while the operation is edit movie
	                if(m.getMovieId() != MovieId)
	                {
		                if(m.getMovieName().equals(MovieName) && m.getMovieBanner().equals(MovieBanner) /* && m.getReleaseDate().equals(ReleaseDate) */ )
		                {
		                	exists = 1;
		                	break;
		                }
	                }
	            	
		         	}
	         	System.out.println("\n RESULT: Movie check - if it is already in the database is done!.");
			 }
			 
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
			
			System.out.println("\n return the result if movie is already in the database!");		
			return exists;	
		}
		catch(SQLException e){
			e.printStackTrace();
		} 	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
		
		return 0;
	}
	
	/*
	 * Create a new movie and add it to the database
	 * It is performed by the Administration unit.
	 * It performs check if the Movie Already there in the databse!
	 */
	public int createMovie(Movie addMovie) throws ServiceUnavailableException,MovieAlreadyExistsException, InvalidEnteriesException
		{
			int isValid = 0;
			int rowcount = 0;
			
			Connection con = null;
			PreparedStatement prestmt = null;
			ResultSet rs = null;
			
		try {
			
			// first check the movie is already in the database or not!
			if(checkMovieExists(addMovie) == 0 )
			{
				//connect to the database
				con = DBConnection.getConnection();
				if(con == null)
				{
					System.out.println("Service unavailable");
					throw new ServiceUnavailableException();	
				}

				System.out.println("\n ------------------------------------------");
				System.out.println("\n Create a new Movie and add it to database!");
				System.out.println("\n ------------------------------------------");
				
				String pStatement = SQLConstants.ADDNEWMOVIE;
				prestmt = con.prepareStatement(pStatement, Statement.RETURN_GENERATED_KEYS);
				
				prestmt.setString(1, addMovie.getMovieName());
				prestmt.setString(2, addMovie.getMovieBanner());
				prestmt.setString(3, addMovie.getReleaseDate());
				prestmt.setDouble(4, addMovie.getRent());
				prestmt.setInt(5,addMovie.getCategorytid());
				prestmt.setInt(6, addMovie.getTotalCopy());
				prestmt.setDouble(7, 0.00);
				prestmt.setInt(8, 0);
				
				prestmt.setInt(9, addMovie.getTotalCopy());
				
				
				System.out.println("\n Query to create a new movie is:" + prestmt.toString());
				
				rowcount = prestmt.executeUpdate();
				if(rowcount > 0)
				{
					isValid = 1;
					System.out.println("\n RESULT: The new movie has been added to the database!.");
				}
				else
				{
					isValid = 0;
					System.out.println("\n RESULT: Error in adding the new movie! Invalid Entries.");	
					throw new InvalidEnteriesException();		
				}
				
				rs = prestmt.getGeneratedKeys();
				int auto_movie_id = 0;
				if (rs.next()) {
					//Get the auto-generated key, which is movie_id in this case.
					auto_movie_id = rs.getInt(1);
					System.out.println("\n The new movie's movie_id " + auto_movie_id);
				}
				else
				{
				System.out.println("\n RESULT: Error in getting generated keys!");
				throw new ServiceUnavailableException();		
				}
				
				// close all the connections.
				System.out.println("\n closing connection to the database!.");
				rs.close();
				prestmt.close();
				con.close();
			}
			else
			{
				System.out.println("\n Movie Already Exists in the databse!.");
				throw new MovieAlreadyExistsException();
			}
				
		}catch(SQLException e){
				throw new InvalidEnteriesException();
			} 	
			finally{
				try{
					rs.close();
				}catch(Exception e){
					
				}
				try{
					prestmt.close();
				}catch(Exception e){
					
				}
				try{
					con.close();
				}catch(Exception e){
					
				}
			}

		System.out.println("\n return the result: 1-successful and 0-unsuccessful!");		
		return isValid;
		}
	
	/*
	 * Edit the movie.
	 * First check if movie with the same name and banner exists in the database.
	 * If yes, don't perform edit operation, else perform edit
	 * 
	 * return: 1 for success
	 * return: 0 for failure
	 */
	public int updateMovie(Movie editMovie) throws ServiceUnavailableException, MovieAlreadyExistsException, InvalidEnteriesException
	{
		int isValid = 0;
		int rowcount = 0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		
		try {
		// first check if the edited details matches with the already existing movie!
		if(checkMovieExists(editMovie) == 0 )
		{
			//connect to the database
			con = DBConnection.getConnection();
			if(con == null)
			{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
			}
			System.out.println("\n ---------------------------------");
			System.out.println("\n Update the movie in the database!");
			System.out.println("\n ---------------------------------");
			
			String pStatement = SQLConstants.EDITMOVIE;
			prestmt = con.prepareStatement(pStatement);
			
			prestmt.setString(1, editMovie.getMovieName());
			prestmt.setString(2, editMovie.getMovieBanner());
			prestmt.setString(3, editMovie.getReleaseDate());
			prestmt.setDouble(4, editMovie.getRent());
			prestmt.setInt(5,editMovie.getCategorytid());
			prestmt.setInt(6, editMovie.getAvailableCopy());
			prestmt.setDouble(7, editMovie.getRating());
			prestmt.setInt(8, editMovie.getNo_of_ratings());
			prestmt.setInt(9, editMovie.getTotalCopy());
			
			//update the movie info where MovieId = ?
			prestmt.setInt(10, editMovie.getMovieId());
			
			System.out.println("\n Query to update the movie is:" + prestmt.toString());
			
			rowcount = prestmt.executeUpdate();
			if(rowcount > 0)
			{
				isValid = 1;
				System.out.println("\n RESULT: The movie has been updated to the database!.");
			}
			else
			{
				isValid = 0;
				System.out.println("\n RESULT: Error in updating the movie!.");
			}
			
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			prestmt.close();
			con.close();
		}
		else
		{
		System.out.println("\n Movie with the same name and banner exists in the database!.");
		throw new MovieAlreadyExistsException();	
		}
		}
		catch(SQLException e){
			throw new InvalidEnteriesException();
		} 	
		finally{
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	System.out.println("\n return the result: 1-successful and 0-unsuccessful!");		
	return isValid;	
	}
	
	
	/*
	 * Deletes an existing movie from the database!
	 * It will first check if the movie has pending issued copies or not!
	 * If there are pending copies, then it does not perform the delete operation!
	 */
	public int deleteMovie(int movieId) throws ServiceUnavailableException, PendingIssuedMoviesException, MovieNotExistsException
	{
		int isValid = 0;
		int rowcount = 0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		
		try {
			
			Movie m = this.getMovieById(movieId);
			
			//STEP 1: check if movie has any pending issued movies!!!
			if(m.getAvailableCopy() == m.getTotalCopy())
			{
			
			//connect to the database
			con = DBConnection.getConnection();
			if(con == null)
			{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
			}

			System.out.println("\n -----------------------------------");
			System.out.println("\n Delete the movie from the database!");
			System.out.println("\n -----------------------------------");
			
			
			//STEP2 : check if the movie is in the order table...as it has the foreign key constraint
			String pStatement = SQLConstants.DELETEMOVIECHECK1;
			prestmt = con.prepareStatement(pStatement);
			
			prestmt.setInt(1, movieId);
			System.out.println("\n Query to check if the movie is in the order table or not is:" + prestmt.toString());
			
			ResultSet rs = null;
			rs = prestmt.executeQuery();
			
			if (rs != null) 
			 {
				// STEP 3: DELETE movie from the order table first!	
				
				pStatement = SQLConstants.DELETEMOVIECHECK2;
				prestmt = con.prepareStatement(pStatement);
				
				prestmt.setInt(1, movieId);
				System.out.println("\n Delete the movie from the order table:" + prestmt.toString());
				
				rowcount = prestmt.executeUpdate();
				if(rowcount>0)
				{
					System.out.println("Successfully deleted movie from the order table!");
				}
				else
				{
					System.out.println("Service unavailable");
					//throw new ServiceUnavailableException();
				}
			}	
			
			// STEP 4: check if the movie is in the queue
			pStatement = SQLConstants.CHECKMOVIEQUEUE1;
			prestmt = con.prepareStatement(pStatement);
			
			prestmt.setInt(1, movieId);
			System.out.println("\n Query to check if the movie is in the user's queue or not is:" + prestmt.toString());
			
			rs = null;
			rs = prestmt.executeQuery();
			
			if (rs != null) 
			 {
				// STEP 3: DELETE movie from the order table first!	
				
				pStatement = SQLConstants.CHECKMOVIEQUEUE2;
				prestmt = con.prepareStatement(pStatement);
				
				prestmt.setInt(1, movieId);
				System.out.println("\n Delete the movie from the queue first:" + prestmt.toString());
				
				rowcount = prestmt.executeUpdate();
				if(rowcount>0)
				{
					System.out.println("Successfully deleted movie from the queue!");
				}
				else
				{
					System.out.println("Service unavailable");
					throw new ServiceUnavailableException();
				}
			}	
			
			//STEP 5: delete movie from movies table....
			pStatement = SQLConstants.DELETEMOVIE;
			prestmt = con.prepareStatement(pStatement);
			
			prestmt.setInt(1, movieId);
			
			System.out.println("\n Query to delete the movie is:" + prestmt.toString());
			
			rowcount = prestmt.executeUpdate();
			if(rowcount > 0)
			{
				isValid = 1;
				System.out.println("\n RESULT: The movie has been deleted from the database!.");
			}
			else
			{
				isValid = 0;
				System.out.println("\n RESULT: Error in deletion of the movie!.");
			}
			
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			prestmt.close();
			con.close();
			}
			else
			{
				System.out.println("\n Movie has been issued. So can't be deleted!");	
				throw new PendingIssuedMoviesException();
			}
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}	
		finally{
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
		
	System.out.println("\n return the result: 1-successful and 0-unsuccessful!");		
	return isValid;	
	}
	
	/*
	 * Get the movie from the system and issue it.
	 *  First, check if the user is eligible to issue the movie.
	 *  Second, check if the movie if available. 
	 *  Third, Check if the user has already issued the same movie and it is still pending for submission!
	 *  
	 *  retunr 1: success
	 *  return 0: failure
	 */
	public int issueMovie(int MovieId,long MembershipId) throws MovieUnavailableException, UserNotEligibleException, ServiceUnavailableException, MovieAlreadyIssuedException, MovieNotExistsException
	{
		int isValid = 0;

		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		
		
		if(this.isUserEligible(MembershipId,1) == 1)
		{
		if(this.isMovieAvailable(MovieId) == 1)
			{
			if(this.movieAlreadyIssued(MovieId,MembershipId) == 1)
			{
				try{
					//connect to the database
					con = DBConnection.getConnection();
					
					System.out.println("\n --------------------------");
					System.out.println("\n Issue a movie to the user!");
					System.out.println("\n --------------------------");
					
					System.out.println("The movie ID is:" + MovieId + ". And the User's Membership ID is: " + MembershipId);
					
			//add the movie to the issued movies list with the user account.(update user's database)
					System.out.println("\n STEP 1: add the movie to issued movies list in database in the user account first!");
					
					String pStatement = SQLConstants.GETISSUEDMOVIES;
					prestmt = con.prepareStatement(pStatement);	
					prestmt.setLong(1, MembershipId);	
					System.out.println("\n Query to get the issued movies from user's account is:" + prestmt.toString());
					
					int issuedmovies=0;
					String issuedmovieslist = "";
					int MovieLimit = 0;
					
					rs = prestmt.executeQuery();
					if (rs.next()) 
						{
						issuedmovies = (int)rs.getInt(1);
						issuedmovieslist = (String)rs.getString(2);
						MovieLimit = (int)rs.getInt(3);
						
						issuedmovies = issuedmovies+1;
						issuedmovieslist = issuedmovieslist + MovieId + ",";
						MovieLimit = MovieLimit-1;
						
						pStatement = SQLConstants.SETISSUEDMOVIES;
						prestmt = con.prepareStatement(pStatement,Statement.RETURN_GENERATED_KEYS);
						
						prestmt.setInt(1, issuedmovies);
						prestmt.setString(2, issuedmovieslist);
						prestmt.setInt(3, MovieLimit);
						prestmt.setLong(4, MembershipId);
						
						System.out.println("\n Query to set the issued movies from user's account is:" + prestmt.toString());
						
						int count = prestmt.executeUpdate();
						
						if(count>0)
							System.out.println("\n RESULT: User's database has been updated!");
						}
					
		//subtract the available copies by 1 in movies table
					System.out.println("\n STEP 2: Subtract the available copies count in movies table!");
					
					pStatement = SQLConstants.GETAVAILABLECOPIES;
					prestmt = con.prepareStatement(pStatement);
					
					prestmt.setInt(1, MovieId);
					System.out.println("\n Query to get available copies:" + prestmt.toString());
					
					int availablecopies=0;
					
					rs = prestmt.executeQuery();
					if (rs.next()) 
						{
						availablecopies = (int)rs.getInt(1);
						availablecopies = availablecopies - 1;
						
						pStatement = SQLConstants.SETAVAILABLECOPIES;
						prestmt = con.prepareStatement(pStatement,Statement.RETURN_GENERATED_KEYS);
						
						prestmt.setInt(1, availablecopies);
						prestmt.setInt(2, MovieId);
						
						System.out.println("\n Query to update the available copies for the movie:" + prestmt.toString());
						int count = prestmt.executeUpdate();
						
						if(count>0)
							System.out.println("\n RESULT: Available copies for the movie has been updated!");
						}
					else
					{
					System.out.println("Service unavailable");
					throw new ServiceUnavailableException();	
					}
					
			//also update the order table....
					System.out.println("\n STEP 3: Add the order to the Order Table!");
					
					//get movie details from id first.
					Movie m = new Movie();
					m = this.getMovieById(MovieId);
					
					
					pStatement = SQLConstants.ADDORDER;
					prestmt = con.prepareStatement(pStatement);
					
					prestmt.setLong(1, MembershipId);
					prestmt.setInt(2, m.getMovieId());
					prestmt.setDouble(3, m.getRent());
					
					System.out.println("\n Query to update the order table is :" + prestmt.toString());
					
					int rowcount = prestmt.executeUpdate();
						if(rowcount > 0)
						{
						System.out.println("\n RESULT: ORDER table updated!");
						}
						else
						{
						System.out.println("Service unavailable");
						throw new ServiceUnavailableException();	
						}
						
			
			//remove movies from user's queue after they have been issued!
						PersonDAO object = new PersonDAO();
						try
						{
						if(object.removefromQueue(m.getMovieId(), MembershipId) == 1)
						{
						
						}
						else
						{
							System.out.println("Service unavailable");	
						}
						}catch(Exception e)
						{
						// do nothing! as the database might not have the queue because user 
						// did not logged out and logged in! Queue will be there when user loggs out and
						// and logs in again!
						}
					isValid = 1;
					System.out.println("\n return the result: 1-successful and 0-unsuccessful!");		
					return isValid;	
				
					}catch(SQLException e){
					e.printStackTrace();
					throw new ServiceUnavailableException();
					} 		
				finally{
					try{
						prestmt.close();
					}catch(Exception e){
						
					}
					try{
						con.close();
					}catch(Exception e){
						
					}
					}
			}
			else
			{
				isValid = 0;
				throw new MovieAlreadyIssuedException();	
			}
			}
			else
			{
			isValid = 0;
			throw new MovieUnavailableException();
			}
		}
		else
		{
		isValid = 0;
		throw new UserNotEligibleException();
		}
	}

	/*
	 * Get the user has already issued the movie and it is still pending for submission!!!
	 */
	public int movieAlreadyIssued(int movieId, long membershipId) throws ServiceUnavailableException
	{
		int result = 0;
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		
		try {
			con = DBConnection.getConnection();

			System.out.println("\n -------------------------------------------------");
			System.out.println("\n Check if the movie is already issued to the user!");
			System.out.println("\n -------------------------------------------------");
			
			String pStatement = SQLConstants.GETISSUEDMOVIES;
			prestmt = con.prepareStatement(pStatement);
			
			prestmt.setLong(1, membershipId);
			
			System.out.println("\n Query to check if the movie is already issued to the user is:" + prestmt.toString());
			
			rs = prestmt.executeQuery();
			
			rs = prestmt.executeQuery();
			if (rs.next()) 
				{
				String issuedmovieslist = (String)rs.getString(2);
				
				String movieIdString = "";
				movieIdString = Integer.toString(movieId)+ ",";
				
				if(issuedmovieslist.contains(movieIdString))
					{
					result = 0;
					System.out.println("\n User has already issued that movie!.");
					}
				else
					{
					result = 1;
					System.out.println("\n User has not issued that movie yet!.");
					}
				}
	        System.out.println("\n RESULT: If user has already issued the movie check is done!.");
			 
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
			return result;
		}catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} 	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	}
	
	
	/*
	 * If the movie is available for issue!
	 */
	public int isMovieAvailable(int MovieId) throws ServiceUnavailableException
	{
		int isAvailable = 0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		
		try {
			con = DBConnection.getConnection();

			System.out.println("\n --------------------------------");
			System.out.println("\n Check if the movie is available!");
			System.out.println("\n --------------------------------");
			
			String pStatement = SQLConstants.MOVIEAVAILABILITY;
			prestmt = con.prepareStatement(pStatement);
			
			//delete the movie where MovieId = ?
			prestmt.setInt(1, MovieId);
			
			System.out.println("\n Query to check if the movie is available or not:" + prestmt.toString());
			
			int Avl_copies = 0;
			
			rs = prestmt.executeQuery();
			
			 if (rs.next()) 
			 {	
	         Avl_copies = (int)rs.getInt(1);
	         		
	         		if(Avl_copies<=0)
						{
						isAvailable = 0;
						}
					else
						{
						isAvailable = 1;
						}		
		      }
	        System.out.println("\n RESULT: Availibility check is done!.");
			 
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
		}catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} 	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}

	System.out.println("\n returning the result.");		
	return isAvailable;	
	}	
	
	/*
	 * Check if the user is eligible to issue the movie or not.
	 */
	public int isUserEligible(long membershipId, int num_movies) throws ServiceUnavailableException	
	{
	int isEligible = 0;

	Connection con = null;
	PreparedStatement prestmt = null;
	ResultSet rs = null;
	
	try {
		con = DBConnection.getConnection();
		if(con == null)
		{
			System.out.println("Service unavailable");
			throw new ServiceUnavailableException();	
		}

		System.out.println("\n ------------------------------");
		System.out.println("\n Check if the User is eligible!");
		System.out.println("\n ------------------------------");
		
		String pStatement = SQLConstants.PERSONELIGIBLE;
		prestmt = con.prepareStatement(pStatement);
		
		//get the movie limit if the MembershipNo is ?
		prestmt.setLong(1, membershipId);
		
		System.out.println("\n Query to check if the user is eligible or not:" + prestmt.toString());
		
		int MovieLimit = 0;
		rs = prestmt.executeQuery();
		
		 if (rs.next()) 
		 {	
			 MovieLimit = (int)rs.getInt(1);
			 System.out.println("\n Movie Limit is:" + MovieLimit);
        		
        		if( MovieLimit<=0)
					{
        			isEligible = 0;
        			System.out.println("\n User is not Eligible!");
					}
				else
				{
					if((MovieLimit-num_movies) < 0)
					{
						isEligible = 0;
						System.out.println("\n User is not Eligible!");
					}
					else
					{
						isEligible = 1;
						System.out.println("\n User is Eligible!");
					}
	         	}		
	      }
		
         System.out.println("\n RESULT: User Eligibility check is done!.");
		// close all the connections.
		System.out.println("\n closing connection to the database!.");
		rs.close();
		prestmt.close();
		con.close();
		System.out.println("\n returning the result.");		
		return isEligible;	
	}catch(SQLException e){
		e.printStackTrace();
		throw new ServiceUnavailableException();
	} 	
	finally{
		try{
			rs.close();
		}catch(Exception e){
			
		}
		try{
			prestmt.close();
		}catch(Exception e){
			
		}
		try{
			con.close();
		}catch(Exception e){
			
		}
	}
	}	
	
	
	
	/*
	 * Customer returns the movie to the system. 
	 */
	public int submitMovie(int movieId,long membershipId) throws ServiceUnavailableException, MovieNotIssuedtoUserException
	{
		int isValid = 0;
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try{
			//connect to the database
			con = DBConnection.getConnection();
			if(con == null)
			{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
			}
				
			System.out.println("\n --------------------------");
			System.out.println("\n User submits the movie!");
			System.out.println("\n --------------------------");
			
			System.out.println("The movie ID is:" + movieId + ". And the User's Membership ID is: " + membershipId);
					
		//subtract the movie from user's account.
			System.out.println("\n STEP 1: subtract the movie from user's issued movie list!");
					
			String pStatement = SQLConstants.GETISSUEDMOVIES;
			prestmt = con.prepareStatement(pStatement);
			
			prestmt.setLong(1, membershipId);
			
			System.out.println("\n Query to get the issued movies from user's account is:" + prestmt.toString());
				
			int issuedmovies=0;
			String issuedmovieslist = "";
			int MovieLimit = 0;
					
			rs = prestmt.executeQuery();
			if (rs.next()) 
				{
				issuedmovies = (int)rs.getInt(1);
				issuedmovieslist = (String)rs.getString(2);
				MovieLimit = (int)rs.getInt(3);
				
				issuedmovies = issuedmovies-1;
				MovieLimit = MovieLimit + 1;
				String movieIdString = "";
				movieIdString = Integer.toString(movieId)+ ",";
				
				if(issuedmovieslist.contains(movieIdString))
					{
					issuedmovieslist = issuedmovieslist.replace(movieIdString, "");
					}
				else
					{
					throw new MovieNotIssuedtoUserException();
					}
				
				pStatement = SQLConstants.SETISSUEDMOVIES;
				prestmt = con.prepareStatement(pStatement);
				
				prestmt.setInt(1, issuedmovies);
				prestmt.setString(2, issuedmovieslist);
				prestmt.setInt(3, MovieLimit);
				prestmt.setLong(4, membershipId);
				
				System.out.println("\n Query to set the issued movies from user's account is:" + prestmt.toString());
				
				int count = prestmt.executeUpdate();
				
				if(count>0)
					System.out.println("\n RESULT: User's database has been updated!");
				}
			else
			{
			System.out.println("Service unavailable");
			throw new ServiceUnavailableException();	
			}
					
				//add the available copies by 1 in movies table
				System.out.println("\n STEP 2: Increase the available copies count by 1 in movies table!");
					
				pStatement = SQLConstants.GETAVAILABLECOPIES;
				prestmt = con.prepareStatement(pStatement,Statement.RETURN_GENERATED_KEYS);
					
				prestmt.setInt(1, movieId);
				System.out.println("\n Query to get available copies:" + prestmt.toString());
					
				int availablecopies=0;
				
				rs = prestmt.executeQuery();
				if (rs.next()) 
					{
					availablecopies = (int)rs.getInt(1);
					
					availablecopies = availablecopies + 1;
					
					pStatement = SQLConstants.SETAVAILABLECOPIES;
					prestmt = con.prepareStatement(pStatement);
						
					prestmt.setInt(1, availablecopies);
					prestmt.setInt(2, movieId);
						
					System.out.println("\n Query to set the available copies for the movie:" + prestmt.toString());
					int count = prestmt.executeUpdate();
						
					if(count>0)
						System.out.println("\n RESULT: Available copies for the movie has been updated!");
					}
				else
					{
					System.out.println("Service unavailable");
					throw new ServiceUnavailableException();	
					}
				
		// update the order table the movie has been submitted now!
				pStatement = SQLConstants.UPDATEORDER;
				prestmt = con.prepareStatement(pStatement);
				
				prestmt.setInt(1, movieId);
				prestmt.setLong(2, membershipId);
				
				System.out.println("\n Query to set the update the order table status is:" + prestmt.toString());
				
				int count = prestmt.executeUpdate();
				
				if(count>0)
					System.out.println("\n RESULT: ORDER TABLE has been updated!");
				else
				{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
				}
				
			isValid = 1;	
			System.out.println("\n RESULT: Movie has been submitted!");	
				
		}catch(SQLException e){
				e.printStackTrace();
				throw new ServiceUnavailableException();
			} 		
			finally{
				try{
				prestmt.close();
				}catch(Exception e){
						
				}
				try{
					con.close();
				}catch(Exception e){
					
				}
				}		
	System.out.println("\n return the result: 1-successful and 0-unsuccessful!");		
	return isValid;	
	}
	
	/*
	 * Generates the bill amount for Simple Customer.
	 */
	public Double generateBillSimple(long membershipId, Movie[] movieList) throws ServiceUnavailableException
	{
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		
		Double balance = 0.0;
		
		try {
			con = DBConnection.getConnection();

			System.out.println("\n ----------------------------------");
			System.out.println("\n Generate Bill for Simple customer!");
			System.out.println("\n ----------------------------------");
			
			String pStatement = SQLConstants.GENERATEBILL_SIMPLE;
			prestmt = con.prepareStatement(pStatement);
			
			//get the user's balance.
			prestmt.setLong(1, membershipId);
			System.out.println("\n Query set user balance is:" + prestmt.toString());
			
			rs = prestmt.executeQuery();
			if (rs.next()) 
				 {
				 balance = ((Double)rs.getDouble(1));
				 }
			
			//update balance and issued movies
			for(int i=0; i<movieList.length ; i++)
			{
				balance = balance + movieList[i].getRent();
			}
			
	         System.out.println("\n RESULT: Balance have been updated!.");
			 
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
		}catch(Exception e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} 	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
			
		System.out.println("\n returning the result.");		
		return balance;	
		}

	/*
	 * Generate bill receipt for Premium customer.
	 * returns the list of the total movie issued for the current month!
	 */
	
	public Movie[] generateBillPremium(long membershipId) throws ServiceUnavailableException
	{
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		
		Movie[] movielist = null;
		
		try {
			con = DBConnection.getConnection();

			System.out.println("\n -----------------------------------");
			System.out.println("\n Generate Bill for Premium customer!");
			System.out.println("\n -----------------------------------");
			
			String pStatement = SQLConstants.GENERATEBILL_PREMIUM;
			prestmt = con.prepareStatement(pStatement);
			
			//get the user's balance and number of issued movies.
			prestmt.setLong(1, membershipId);
			System.out.println("\n Query get all issued movies for current month is:" + prestmt.toString());
			
			int size = 0;
			
			rs = prestmt.executeQuery();
			if (rs != null) 
			 {
	         rs.last();
	         
	         size = rs.getRow();
	         movielist = new Movie[size];
	         
	         int currRow = 0;
	         
	         rs.beforeFirst();
	         	while (rs.next())
		         	{	
	                Movie movie = new Movie();
					int movieId = (int)rs.getInt(1);
					movie.setMovieId(movieId);
						try
						{
							movie = this.getMovieById(movieId);
						}catch(MovieNotExistsException e)
						{
							System.out.println("Movie has been deleted from database");
						}
					movie.setRentdate((String)rs.getString(2));
					
					movielist[currRow] = movie;
					currRow++;
		         	}
	         System.out.println("\n RESULT: List of all the movies for the current month!.");
			 }
						 
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
		}catch(SQLException e){
			e.printStackTrace();
		} 	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
			
		System.out.println("\n returning the result.");		
		return movielist;	
		}
	
	
	/*
	 * Returns the movie details by MovieId
	 */
	public Movie getMovieById(int MovieId) throws ServiceUnavailableException, MovieNotExistsException
	{
	Movie m = null;
	
	Connection con = null;
	PreparedStatement prestmt = null;
	ResultSet rs = null;
	try {
		//connect to the database
		con = DBConnection.getConnection();
		if(con == null)
		{
			System.out.println("Service unavailable");
			throw new ServiceUnavailableException();	
		}

		System.out.println("\n ----------------------------");
		System.out.println("\n Get Movie from the Movie ID!");
		System.out.println("\n ----------------------------");
		
		String pStatement = SQLConstants.GETMOVIEBYID;
		prestmt = con.prepareStatement(pStatement);
		
		prestmt.setInt(1, MovieId);
		
		System.out.println("\n Query to get the Movie depending on Movie Id is:" + prestmt.toString());
		
		rs = prestmt.executeQuery();
		if(rs.next())
	         	{
				m = new Movie();
				m.setMovieId(MovieId);
				m.setMovieName((String)rs.getString(1));
				m.setMovieBanner((String)rs.getString(2));
				m.setReleaseDate((String)rs.getString(3));
				m.setRent((Double)rs.getDouble(4));
				m.setCategoryid((Integer)rs.getInt(5));	
				m.setRating((Double)rs.getDouble(6));
				m.setNo_of_ratings((Integer)rs.getInt(7));
				m.setAvailableCopy((Integer)rs.getInt(8));
				m.setTotalCopy((Integer)rs.getInt(9));
				
				if(m.getAvailableCopy()<=0)
					{
					m.setStatus(notAvailable);
					}
				else
					{
					m.setStatus(available);
					}		
	         	}
		else
		{
			throw new MovieNotExistsException();	
		}
         System.out.println("\n RESULT: Category Name for the specific category has been fetched!.");
		
        // close all the connections.
        System.out.println("\n closing connection to the database!.");
		rs.close();
		prestmt.close();
		con.close();
		
	}
	catch(SQLException e){
		e.printStackTrace();
	} 	
	finally{
		try{
			rs.close();
		}catch(Exception e){
			
		}
		try{
			prestmt.close();
		}catch(Exception e){
			
		}
		try{
			con.close();
		}catch(Exception e){
			
		}
	}
	return m;
	}
	
	/*
	 * Get the list of the movies issued by the user and still pending for submission!
	 */
	public Movie[] getUserIssuedMovies(long membershipId) throws ServiceUnavailableException, UserHasNotIssuedAnyMovieException
	{
		Movie[] movielist = null;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			con = DBConnection.getConnection();
			if(con == null)
			{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
			}

			System.out.println("\n --------------------------------------");
			System.out.println("\n Get list of movies issued by the user!");
			System.out.println("\n --------------------------------------");
			
			String pStatement = SQLConstants.GETISSUEDMOVIES;
			
			prestmt = con.prepareStatement(pStatement);	
			prestmt.setLong(1, membershipId);	
			System.out.println("\n Query to get the issued movies from user's account is:" + prestmt.toString());
			
			String issuedmovieslist = "";
			
			rs = prestmt.executeQuery();
			if (rs.next()) 
				{
				issuedmovieslist = (String)rs.getString(2);
				
				if(issuedmovieslist.contains(","))
				{
				System.out.println("\n Issued movie Ids for the user are:" + issuedmovieslist);
				String delimiter = ",";
				String[] ids;
				ids = issuedmovieslist.split(delimiter);
				int[] id = new int[100];
			
						int i=0;
						for(i=0; i<ids.length ;i++)
						{
						id[i] = Integer.parseInt(ids[i]);
						}
						
						movielist = new Movie[ids.length];
						for(int j=0;j<ids.length;j++)
						{
						Movie m = new Movie();
						m.setMovieId(id[j]);
							try
							{
							m = this.getMovieById(id[j]);
							}catch(MovieNotExistsException e)
							{
								System.out.println("\n movie has been deleted from databse!");
							}
						//get the rent date
						String rentdate = this.getMovieLatestRentDate(m.getMovieId(), membershipId);
						m.setRentdate(rentdate);
						movielist[j] = m;
						}
				}
				else
				{
					System.out.println("\n No issued movies for the user!.");
					throw new UserHasNotIssuedAnyMovieException();
				}
				}
		
		
			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
		}
		catch(SQLException e){
			e.printStackTrace();
			return movielist;
		}	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}	
		return movielist;
	}
	
	
	/*
	 * Get the rent date for issued movie(still pending to submit) for the user!
	 */
	public String getMovieLatestRentDate(int movieId, long memshipId) throws ServiceUnavailableException
	{
		String Rentdate = "";
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		
		try {
			con = DBConnection.getConnection();
			if(con == null)
			{
				System.out.println("Service unavailable");
				throw new ServiceUnavailableException();	
			}

			System.out.println("\n --------------------------------------------------------");
			System.out.println("\n Get the latest rent date for the movie from order table!");
			System.out.println("\n --------------------------------------------------------");
			
			String pStatement = SQLConstants.GETRENTDATE;
			prestmt = con.prepareStatement(pStatement);
			
			prestmt.setLong(1, memshipId);
			prestmt.setInt(2, movieId);
			
			System.out.println("\n Query to get the rent date for the movie is:" + prestmt.toString());
			
			rs = prestmt.executeQuery();
			
			if (rs != null) 
			 {
	         rs.last();
	         Rentdate = (String)rs.getString(1);
	         System.out.println("\n RESULT: Rent date is:" + Rentdate);
		     }
	         	
	        System.out.println("\n RESULT: Rent date has been fetched!.");

			// close all the connections.
			System.out.println("\n closing connection to the database!.");
			rs.close();
			prestmt.close();
			con.close();
		}catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} 	
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	return Rentdate;
	}	
}